package com.xsmart.oa.dao;

import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author pansheng
 */
@Component
public interface DistributeOrderExcelDao {


    /**  查询订单报表信息
     * @param page
     * @param paramMap
     * @return
     */
    @Select("<script>" +
            "select oi.order_id,oi.order_sn,dm.d_user_id,oi.user_id,oi.shipping_id,oi.pay_id,ss.shop_name, iu.nick_name," +
            "sum(og.cost_price*og.goods_number)GoodsAmount ," +
            "oi.postscript,oi.goods_amount,oi.shipping_fee," +
            "osi.best_time,osi.tel,osi.sign_building,osi.zipcode,osi.email," +
            "oi.money_paid,oi.integral,oi.integral_money,oi.bonus,oi.order_amount,p.pay_name,s.shipping_name,iu.user_name,osi.consignee,osi.address,osi.mobile, " +
            "DATE_FORMAT(oi.confirm_time,'%Y-%m-%d %T')confirm_time," +
            "DATE_FORMAT(oi.pay_time,'%Y-%m-%d %T')pay_time," +
            "DATE_FORMAT(oi.shipping_time,'%Y-%m-%d %T')shipping_time," +
            "(CASE WHEN oi.order_status = '0' THEN '未确认' WHEN oi.order_status = '1' THEN '确认' WHEN oi.order_status = '2' THEN ' 已取消' WHEN oi.order_status = '3' THEN ' 无效' WHEN oi.order_status = '4' THEN ' 退货' WHEN oi.order_status = '5' THEN ' 已删除' END)order_status," +
            "(CASE WHEN oi.pay_status = '0' THEN '未付款' WHEN oi.pay_status = '1' THEN '付款中' WHEN oi.pay_status = '2' THEN ' 已付款' END)pay_status," +
            "date_format(oi.add_time,'%Y-%m-%d %T') add_time from order_info oi " +
            "left outer join infu_distributor_member dm on dm.user_id=oi.user_id " +
            "left outer join INFS_USER iu on dm.user_id = iu.user_id " +
//            "left outer join INFS_USER iu on oi.user_id = iu.user_id " +
            "left outer join seller_shopinfo ss on ss.ru_id=dm.d_user_id " +
            "left outer join order_goods og on og.order_id=oi.order_id " +
            "left outer join goods g on g.goods_id=og.goods_id  " +
            "LEFT outer JOIN payment p on oi.pay_id =p.pay_id " +
            "LEFT outer JOIN order_shipping_info osi on oi.order_id = osi.order_id " +
            "LEFT outer JOIN shipping s on oi.shipping_id = s.shipping_id " +
            "where 1=1 " +
            "<if test=\"nickName!=null and nickName!=''\">and iu.nick_name like concat('%',#{nickName},'%') </if> " +
            "<if test=\"orderSn!=null and orderSn!=''\">and oi.order_sn = #{orderSn} </if> " +
            "<if test=\"orderId!=null and orderId!=''\">and oi.order_id = #{orderId} </if> " +
            "<if test=\"orderStatus!=null and orderStatus!=''\">and oi.order_status = #{orderStatus} </if> " +
            "<if test=\"shippingStatus!=null and shippingStatus!=''\">and oi.shipping_status = #{shippingStatus} </if> " +
            "<if test=\"payStatus!=null and payStatus!=''\">and oi.pay_status = #{payStatus} </if> " +
            "<if test=\"addStartTime!=null and addStartTime!=''\">and oi.add_time &gt; #{addStartTime} </if> " +
            "<if test=\"addEndTime!=null and addEndTime!=''\">and oi.add_time &lt; #{addEndTime} </if>"+
            "<choose> " +
            "<when test='timeType==1'>and to_days(oi.add_time) = to_days(now())</when>" +
            "<when test='timeType==2'>and (DATE_SUB(CURDATE(), INTERVAL 3 DAY) &lt; date(oi.add_time) or to_days(oi.add_time) = to_days(now()))</when>" +
            "<when test='timeType==3'>and YEARWEEK(date_format(oi.add_time,'%Y-%m-%d')) = YEARWEEK(now())</when>" +
            "<when test='timeType==4'>and DATE_FORMAT(oi.add_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )</when>" +
            "<when test='timeType==5'>and (PERIOD_DIFF(date_format( now() , '%Y%m' ) , date_format(oi.add_time, '%Y%m')) in (0,1,2) " +
            ")</when>" +
            "</choose>" +
            "GROUP BY oi.order_id " +
            "ORDER BY oi.add_time asc" +
            "</script>")
    List<Map<String,Object>> queryOrderExcel(Pagination page,Map paramMap);


}
